# 填充开始 https://www.bilibili.com/video/BV1W7411G7wP/?p=4&spm_id_from=pageDriver&vd_source=8bd7b24b38e3e12c558d839b352b32f4
# 日期处理 https://www.bilibili.com/video/BV1W7411G7wP/?p=5&spm_id_from=pageDriver&vd_source=8bd7b24b38e3e12c558d839b352b32f4
# 读Books.xlsx
import pandas as pd
from datetime import date, timedelta
import copy

# 有空行和空列的情况肯定读取是不正常的。
#books = pd.read_excel('../resources/input/Books.xlsx')
#print(books)

# 跳过空行和空列
books = pd.read_excel('../resources/input/Books.xlsx'
                      , skiprows=3
                      , usecols="C:F"
                      , dtype={'ID':str, 'InStore': str, 'Date':str})
print(books)
print(books['ID'])
print(type(books['ID']))

# 尝试填充
books['ID'].at[0] = 100
print(books)
books2 = copy.copy(books) # 深拷贝。否则就是传递引用。

# 填充ID
start = date(2024, 1, 1)

def add_month(d, md):
    yd = md // 12
    m = d.month + md % 12
    if m != 12:
        yd += m // 12
        m = m % 12
    return date(d.year + yd, m, d.day)

# 写法1 通过Series写
for i in books.index:
    books['ID'].at[i] = i + 1
    books['InStore'].at[i] = 'Yes' if i % 2 == 0 else 'No'
    #books['Date'].at[i] = start + timedelta(days=i) # 日填充
    #books['Date'].at[i] = date(start.year + i, start.month, start.day)  # 年填充
    books['Date'].at[i] = add_month(start, i) # 月填充

print(books)
books.set_index('ID', inplace=True)# 不再打印自动生成的ID那一列
books.to_excel('../resources/output/Books_Series.xlsx')


# 写法2 直接通过DateFrame
print('============================')
print(books2)
for i in books2.index:
    books2.at[i, 'ID'] = i + 1
    books2.at[i, 'InStore'] = 'Yes' if i % 2 == 0 else 'No'
    books2.at[i, 'Date'] = add_month(start, i)
print(books2)
books2.set_index('ID', inplace=True)# 不再打印自动生成的ID那一列
books2.to_excel('../resources/output/Books_DataFrame.xlsx')